#!/usr/bin/perl

use strict;
use warnings;
use utf8;

use Data::Dumper;

use Encode qw(decode encode);
use String::CRC32;
use Config::General;
use File::Spec::Functions;
use FindBin;
use DBI;

my $dir = catfile($FindBin::Bin, '..', 'data');
my %files = (
	rus_fix => 'kolesnikov_isupnummap_abc.csv',
	rus_mob => 'kolesnikov_isupnummap_def.csv',
	foreign => 'kolesnikov_isupnummap_abr.csv'
);

my $db_conf_key = 'StatDB';
my %conf = Config::General->new( catfile($FindBin::Bin, '..', 'mtt.conf') )->getall();
die 'Database config not found' unless $conf{$db_conf_key} && $conf{$db_conf_key}->{connect_info};
my $ci = $conf{$db_conf_key}->{connect_info};

my $dbh = DBI->connect(
        $ci->{dsn},
        $ci->{user},
		$ci->{password}
) || die DBI->errstr;

$dbh->do( $ci->{on_connect_do} ) if $ci->{on_connect_do};
$dbh->{mysql_enable_utf8} = $ci->{mysql_enable_utf8} if $ci->{mysql_enable_utf8};



$dbh->do('DROP TABLE IF EXISTS masks');

$dbh->do('
	CREATE TABLE IF NOT EXISTS masks(
		`mask` INT UNSIGNED PRIMARY KEY,
		`country_code` INT UNSIGNED,
		`code1` INT UNSIGNED DEFAULT NULL,
		`code2` INT UNSIGNED DEFAULT NULL,
		`fake_mask` varchar(3) DEFAULT NULL,
		`country` varchar(40) NOT NULL,
		`area` varchar (42) DEFAULT NULL,
		`region` varchar(42) DEFAULT NULL,
		`city` varchar(55) DEFAULT NULL,
		`operator` varchar(50) DEFAULT NULL,
		`type` char(1) NOT NULL,
		`crc`  varchar(10) NOT NULL,
		KEY `mask`(`mask`),
		KEY `crc`(`crc`),
		KEY `country_code`(`country_code`),
		KEY `code1`(`code1`),
		KEY `code2`(`code2`),
		KEY `country`(`country`),
		KEY `city`(`city`),
		KEY `operator`(`operator`),
		KEY `area`(`area`),
		KEY `region`(`region`)
	)ENGINE=MyISAM DEFAULT CHARSET=utf8'
) || die $dbh->errstr;


my $sth = $dbh->prepare(
	'INSERT INTO masks(mask, country_code, code1, code2, fake_mask, '
	. 'country, area, region, city, operator, type, crc) '
	. 'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
);

# Российские мобильные
#
#

open(F, catfile($dir, $files{rus_mob})) || die $!;
while (<F>) {
	next unless /^\d+/;

	my $str = decode('cp1251', $_);
	my (undef, $from, $to, $operator, $city, $fo, $group) = split /;/, $str;
	my ($country, $country_code) = ('Россия', 7);

	$operator =~ s/["']//g;

	my ($l, $r) = ($from =~ /(0+)$/ , $to =~ /(9+)$/ );
	($l, $r) = ( length($l), length($r) );
	my $len = length($to) - 3 - ( $l <= $r ? $l : $r );
	
	my ($code1, $code2) = $from =~ /^(\d{3})(\d{$len})/; 
	my ($code_test, $code_high) = $to =~/^(\d{3})(\d{$len})/; 

	($code2, $code_high) = (undef, undef) if 0 == $len;
#	die "code1 not equal: $code1 - $code_test, $operator" if $code1 ne $code_test;

	while ( int($code2 || 0) <= int($code_high || 0) ) {
		$sth->execute(
			$country_code . $code1 . ($code2 || ''),
			$country_code,
			$code1,
			$code2,
			'mr',
			$country,
			$fo,
			'',
			$city,
			$operator,
			'm',
			crc32($country . $code1 . $operator . $city)
		);

		$code2++; 
		$code2 = sprintf("%0${len}s", $code2);
	}
}

close F;


# Российские фиксированные
#
#

open(F, catfile($dir, $files{rus_fix})) || die $!;
while (<F>) {
	next unless /^\d+/;

	my $str = decode('cp1251', $_);
	my (undef, $code1, $code2, $city, $region, $fo) = split /;/, $str;
	my ($country, $country_code) = ('Россия', 7);

	if ( $city =~ /Москва|Петербург/ ) {
		$code2 = 0;
		while ( $code2++ < 9 ) {
			$sth->execute(
				$country_code . $code1 . ($code2 || ''),
				$country_code,
				$code1,
				$code2 || undef,
				'fr',
				$country,
				$fo,
				$region,
				$city || '',
				'',
				'f',
				crc32($country . $code1 . ($code2 || '') . ($city || ''))
			);
		}

		next;
	}

	$sth->execute(
		$country_code . $code1 . ($code2 || ''),
		$country_code,
		$code1,
		$code2 || undef,
		'fr',
		$country,
		$fo,
		$region,
		$city || '',
		'',
		'f',
		crc32($country . $code1 . ($code2 || '') . ($city || ''))
	);
}

close F;


# Иностранные
#
#
open(F, catfile($dir, $files{foreign})) || die $!;
while (<F>) {
	next unless /^\d+/;

	my $str = decode('cp1251', $_);
	my (undef, $country_code, $descr, $type, $code1, $country) = split /;/, $str;

	$type = 1 == $type ? 'm' : 'f'; 

	$country =~ s/[^\w]+$//;

	my $operator;
	$descr =~ /,([^,]+)$/ 
	|| $descr =~ /(?i:моб)\.\s+(.+)$/ 
	|| $descr =~ /\w+?([А-ЯA-Z]\w+)$/;

	if ($1) {
		$operator = $1;
		$operator =~ s/^\s+//;
		$operator =~ s/\s+$//;
		$operator =~ s/\bOthers\b//i;
		$operator =~ s/\bмоб\b\.?$//i;
		$operator =~ s/\bроуминг\b\s*//i;
		$operator =~ s/\b(прочие|рочие)\b\s*//i;
	}

	$operator =~ s/["']//g;

	undef $operator unless $operator;

	$sth->execute(
		$country_code . $code1,
		$country_code,
		$code1 || undef,
		undef,
		'mn',
		$country,
		undef,
		undef,
		undef,
		$operator,
		$type,
		crc32($country_code . $country . ($operator || ''))
	);
}

close F;


__END__
my $outfile = 'masks_load';
open(OUT, '>:encoding(UTF-8)', $outfile) || die $!;

print OUT "7\t7\tNULL\tNULL\tfr\tРоссия\t\t\t\t\tf\t\n";
print OUT "7800\t7\t800\tNULL\tfr\tРоссия\t\t\t\t\tf\t999999999\n";

my %masks = ();

open(F, '<:encoding(UTF-8)', "${dir}DEF_national.csv") || die $!;
while (<F>) {
	next unless /[\w]/;
	s/"//g;
	my @row = split /\t/;

	my ($country, $code1, $code2, $region, $area, $city, $operator, $type) = 
			('Россия',$row[5], $row[6], $row[1], $row[7], $row[0], $row[4], 'm');

	foreach ($country, $code1, $code2, $area, $region, $city, $operator, $type)	{
		s/(?:^\s+|\s+$)//g;
	}

	next unless $code1 =~ /^\d+$/;

	my $country_code = '7';

	$country =~ s/\s+Моб\.?\s*$//i;

	my $masks = split_code( $code2 );
	foreach my $c2 ( @$masks ) {
		next if $masks{ $country_code . $code1 . $c2 };
		$masks{ $country_code . $code1 . $c2 } = [ $country_code, $country, $area, $region, $city, $operator, $code1, $c2, $type ];
	}

}
close F;

foreach my $mask ( keys %masks ) {
	my ($country_code, $country, $area, $region, $city, $operator, $code1, $code2, $type) = @{ $masks{$mask} };
	my $crc = crc32("$country$code1$operator$city");

	if ($code2 == 0) 
	    {
	    $mask =~ s/$code2$//;
	    $code2 = "NULL";
	    }
	if ($code1 == 0) 
	    {
	    $mask =~ s/$code1$//;
	    $code1 = "NULL";
	    }
	
	print OUT "$mask\t$country_code\t$code1\t$code2\tmr\t$country\t$area\t$region\t$city\t$operator\t$type\t$crc\n";
}

%masks = ();
open(F, '<:encoding(UTF-8)', "${dir}ABC_national.csv") || die $!;
while (<F>) {
	next unless /[\w]/;
	s/"//g;

	my @row = split /\t/;

	my ($country_code, $country, $region, $city, $code1, $type) = (7, 'Россия', $row[2], $row[3], $row[4], 'f');
	

	foreach ($country, $code1, $region)	{
		s/(?:^\s+|\s+$)//g;
	}

#	next unless $code1 =~ /^\d+$/;
	$country =~ s/\s+Моб\.?\s*$//i;
		
	if ( $code1 == 495 || $code1 == 499 || $code1 == 812 ) {
		foreach (1..9) {
			$masks{ $country_code . $code1 . $_ } = [ $country_code, $country, $region, $city, $code1, $_, $type ];
		}
	}
	else {
		my $code2 = 'NULL';
		if ( $code1 =~ /^\s*(\d{3})\s*\(([^)]+)/ ) {
			($code1, $code2) = ($1, $2);
			my $masks = split_code($code2);
			foreach my $c2 ( @$masks ) {
				next if $masks{ $country_code . $code1 . $c2 };
				$masks{ $country_code . $code1 . $c2 } = [ $country_code, $country, $region, $city, $code1, $c2, $type ];
			}
		}
		elsif ( $code1 =~ /^\s*(\d{3})(\d+)/ ) {
			($code1, $code2) = ($1, $2);
			next if $masks{ $country_code . $code1 . $code2 };
			$masks{ $country_code . $code1 . $code2 } = [ $country_code, $country, $region, $city, $code1, $code2, $type ];
		}
		else {
			next if $masks{ $country_code . $code1 };
			$masks{ $country_code . $code1 } = [ $country_code, $country, $region, $city, $code1, 'NULL', $type ];
		}
	}
}
close F;

foreach my $mask ( keys %masks ) {
	my ($country_code, $country, $region, $city, $code1, $code2, $type) = @{ $masks{$mask} };
	my $crc = crc32("$country$code1$code2$city");


	if ($code2 == 0) 
	    {
	    $mask =~ s/$code2$//;
	    $code2 = "NULL";
	    }
	if ($code1 == 0) 
	    {
	    $mask =~ s/$code1$//;
	    $code1 = "NULL";
	    }


	print OUT "$mask\t$country_code\t$code1\t$code2\tfr\t$country\t\t$region\t$city\t\t$type\t$crc\n";
}

%masks = ();
my ($s_store, $s_country) = ('', '');
open(F, '<:encoding(UTF-8)', "${dir}ABC_international.csv") || die $!;
while (<F>) {

	s/"//g;
	next unless /\w/;
	my @row = split /\t/;

	my ($country_code, $country, $code1, $code2, $area, $region, $city, $operator, $type);

	($country_code, $country, $code1, $code2, $area, $type) = ($row[3], $row[0], $row[4], $row[5], $row[2], 'f');
#	($area, $region) = $row[2] =~ /^\s*([^,]+),?\s*(.*)\s*$/;

	if ( $country_code ) {
		$s_store = $country_code;
	}
	else {
		$country_code = $s_store;
	}

	if ( $country ) {
		$s_country = $country;
	}
	else {
		$country = $s_country;
	}
	
	foreach ($country, $code1, $code2, $area, $region, $city, $operator, $type)	{
		s/(?:^\s+|\s+$)//g;
	}

	next unless $country_code =~ /^\d+$/;

	$country =~ s/\s+Моб\.?\s*$//i;
	my $fake = 'mn';

	if ( $code2 =~ /\d/ ) {
		$type = 'm';
		$code2 =~ s/[^\d,()-]//g;

		my $masks = split_code($code2);
		foreach my $c1 ( @$masks ) {
			next if $masks{ $country_code . $c1 };
			$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, $type, $fake . $type ];
		}
	}
#	elsif ( $code1 =~ /[(,-]/ ) {
#		$masks = split_code($code1);
#		foreach my $c1 ( @$masks ) {
#			next if $masks{ $country_code . $c1 }++;
#			print OUT "$country_code$c1\t$country_code\t\t\t\t$country\t$area\t$region\t$city\t$operator\t$type\n";
#		}
#	}
	elsif ( $country_code =~ /[(-]/ ) {
		my $masks = split_code($country_code);
		($country_code) = $country_code =~ /^\s*(\d+)/;

		foreach my $c1 ( @$masks ) {
			next if $masks{ $country_code . $c1 };
			$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, 'f', $fake ];
#			print OUT "$c1\t$country_code\t\t\t\t$country\t$area\t$region\t$city\t$operator\t$type\n";
		}
	}
	else {
		if ( $country =~ /США/i || $country =~ /Канада/i 
				|| $country =~ /Казахстан/i || $country =~ /Грузия/i ) {
			my $masks = split_code($code1);
			foreach my $c1 ( @$masks ) {
				next if $masks{ $country_code . $c1 };
				$masks{ $country_code . $c1 } = [ $country_code, $c1, $country, 'f', $fake ];
			}
		}
		else {
			next if $masks{ $country_code };
			$masks{ $country_code } = [ $country_code, '', $country, 'f' ];
		}
		
#		print OUT "$country_code$code1\t$country_code\t\t\t\t$country\t$area\t$region\t$city\t$operator\t$type\n";
	}

}
close F;

foreach my $mask ( keys %masks ) {
	my ($country_code, $c1, $country, $type, $fake, $operator) = @{ $masks{$mask} };  #ADD OPERATOR
	my $crc = crc32("$country_code${country}${operator}");

######################### ALL MN ##########################################################
#####	print OUT "$mask\t$country_code\t$c1\t\t$fake\t$country\t\t\t\t\t$type\t$crc\n";
	print OUT "$mask\t$country_code\t$c1\t\tmn\t$country\t\t\t\t\t$type\t$crc\n";
}

close OUT;

my $res = `mysql -u root -pa1s3an -D mtt -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks "`;


#my $res = `mysql -u root -p123 -D stat -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;
#my $res = `mysql -ulexa -pcallsmtt -Dcalls -h 10.77.30.138 -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;
#my $res = `mysql -ulexa -pcallsmtt -Dtemp_stat -h 10.77.30.136 -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;
#my $res = `mysql -u root -p123 -D mtt -e "LOAD DATA LOCAL INFILE \\\"$outfile\\\" INTO TABLE masks"`;

$dbh->do('UPDATE masks SET code2 = NULL where code2 = 0');
$dbh->do('UPDATE masks SET code1 = NULL where code1 = 0');



sub split_code {
	my ($str) = @_;
	my $res = [];

	my ($prefix, $c1) = ('', '');
	if ( $str =~ /\d+\s*\(/ ) {
		($prefix, $c1) = $str =~ /^(\d+)\s*\(\s*([^)]+)/;
		($c1, $prefix) = ($prefix, '') if $c1 =~ /[^\d,\s-]/;
	}
	else {
		$c1 = $str;
	}
	foreach my $p ( split /\s*,\s*/, $c1 ) {
		my ($c2, $c3) = split /\s*\-\s*/, $p;
		if ($c3) {
			($c2, $c3) = ( int($c2), int($c3) );
			my $l = length $c3;
			while ($c2 <= $c3) {
				push @$res, sprintf("%s%0${l}s", $prefix, $c2++);
			}
		}
		else {
			push @$res, $prefix . $c2;
		}
	}

	return $res;
}
